﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for DBAcess
/// </summary>
public class UtilObjectData
{
    private static string sqlString;
    private static SqlConnection con;
    private static DataSet ds;
    private static string query;
    public UtilObjectData()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public static List<Catalog> getCatalog(int os,int parentID)
    {
        ds = new DataSet();
        con = MyConnection.get();
        query = "select * from Catalog where parentID = " + parentID + " and operatorID = " + os;
        SqlCommand command = new SqlCommand(query, con);
        command.CommandType = CommandType.Text;
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            List<Catalog> catalogs = new List<Catalog>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Catalog c = new Catalog();
                c.Name = row["Name"].ToString();
                c.OperatorID = os;
                c.IdCatalog = (int)row["idCatalog"];
                c.ParentID = (int)row["ParentId"];
                c.Childs = getCatalog(os, c.IdCatalog);
                catalogs.Add(c);

            }
            return catalogs;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
        }
    }
    public static List<string> Test(string x)
    {
        ds = new DataSet();
        con = MyConnection.get();
        query = "select * from Catalog where parentID = 1 and operatorID = 1";
        SqlCommand command = new SqlCommand(query, con);
        command.CommandType = CommandType.Text;
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            List<string> l = new List<string>();
            l.Add("dsfsd");
            l.Add("dsfsdfds");
            return l;
        }
        catch {
            return null;
        }
        finally
        {
            con.Close();
        }
    }
    public static List<SoftwareDTO> selectTop5(int top,int os,int catalogID,int free,bool download1, bool download2,bool topRating, bool topNew){
        ds = new DataSet();
        con = MyConnection.get();
        string strCatalog = "";
        List<Catalog> catalogs = getCatalog(os, catalogID);
        List<int> ids = getIDCatalog(catalogs);        
        foreach(int i in ids)
        {
            strCatalog+=(","+i);
        }
        strCatalog = strCatalog.Substring(1);

        query = "SELECT TOP (" + top + ") *, ((Rating5 * 4) + (Rating4 *2) + Rating3  - (Rating1*4) - (Rating2 * 2) ) AS RATING FROM  dbo.Software where CatalogID IN (" + strCatalog + ") AND";
        if (free==0)
            query += " Price = 0 AND";
        else if(free==1)
            query += " Price > 0 AND";
        query = query.Substring(0, query.LastIndexOf("AND"));
        query+=" ORDER BY ";
        if(download1)
            query+="Download1 DESC,";
        if(download2)
            query+="Download2 DESC,";
        if(topRating)
            query += "RATING DESC,";
        if (topNew)
            query += "DateUpgrade DESC,";
        if(query.LastIndexOf(" ORDER BY ")>(query.Length-7))
            query= query.Substring(0, query.LastIndexOf("ORDER BY "));
        else
            query = query.Substring(0, query.LastIndexOf(","));
        SqlCommand command = new SqlCommand(query, con);
        command.CommandType = CommandType.Text;
        try
        {
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            List<SoftwareDTO> softwares = new List<SoftwareDTO>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                SoftwareDTO soft = new SoftwareDTO();
                soft.Name = row["Name"].ToString();
                soft.Version= row["Version"].ToString();
                soft.DateUp = (DateTime)row["DateUpload"];
                soft.DateUpdrade = (DateTime)row["DateUpgrade"];
                soft.Size = (double)row["Size"];
                soft.AcountUp = row["AcountUpload"].ToString();
                soft.Company = row["Company"].ToString();
                soft.CatalogID = (int)row["CatalogID"];
                soft.Price = (int)row["Price"];
                soft.InfoNewVersion = row["InfoNewVer"].ToString();
                soft.Info = row["Info"].ToString();
                soft.Download1 = (int)row["Download1"];
                soft.Download2 = (int)row["Download2"];
                soft.Rate1 = (int)row["Rating1"];
                soft.Rate2 = (int)row["Rating2"];
                soft.Rate3 = (int)row["Rating3"];
                soft.Rate4 = (int)row["Rating4"];
                soft.Rate5 = (int)row["Rating5"];
                soft.Link = row["Link"].ToString();
                soft.Icon = row["Icon"].ToString();
                soft.TagSearch = row["Tag"].ToString();
                softwares.Add(soft);
            }
            return softwares;
        }
        catch
        {
            return null;
        }
        finally
        {
            con.Close();
        }
    }

    private static List<int> getIDCatalog(List<Catalog> catalogs)
    {
        List<int> idCatalogs = new List<int>();
        foreach(Catalog c in catalogs){
            idCatalogs.Add(c.IdCatalog);
            if(c.Childs!=null && c.Childs.Count>0)
                idCatalogs.AddRange(getIDCatalog(c.Childs));
        }
        return idCatalogs;
    }
}